﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MagicBag.CRM.Model;
using MagicBag.CRM.IDAL;
using System.Data.SqlClient;
using System.Data;
using MagicBag.CRM.Common;

namespace MagicBag.CRM.SqlServerDAL
{
    public class DepartmentDAL : IDepartment
    {
        public int Insert(Department dept)
        {
            string sql = "insert into [dbo].[Department] ([CName] ,[EName],[ParentID],[Remark]) values(@CName,@EName,@ParentID,@Remark)";
            SqlParameter[] paras = new SqlParameter[4];
            paras[0] = new SqlParameter("@CName", SqlDbType.NVarChar, 50);
            paras[1] = new SqlParameter("@EName", SqlDbType.VarChar, 100);
            paras[2] = new SqlParameter("@ParentID", SqlDbType.Int);
            paras[3] = new SqlParameter("@Remark", SqlDbType.NVarChar, 500);

            paras[0].Value = dept.CName;
            paras[1].Value = dept.EName;
            paras[2].Value = dept.ParentID;
            if (dept.ParentID == 0)
                paras[2].Value = DBNull.Value;
            else
                paras[2].Value = dept.ParentID;
            paras[3].Value = dept.Remark;


            using (SqlConnection conn = new SqlConnection(SqlHelper.deptConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        public int Delete(int id)
        {
            string sql = "delete from [dbo].[Department] where [ID]=@ID";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@ID", SqlDbType.Int);
            paras[0].Value = id;
            using (SqlConnection conn = new SqlConnection(SqlHelper.deptConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        public int Update(Department dept)
        {
            string sql = "update [dbo].[Department] set[CName]= @CName,[EName]=@EName,[ParentID]=@ParentID,[Remark]=@Remark where [ID]=@ID";
            SqlParameter[] paras = new SqlParameter[5];
            paras[0] = new SqlParameter("@CName", SqlDbType.NVarChar, 50);
            paras[1] = new SqlParameter("@EName", SqlDbType.VarChar, 100);
            paras[2] = new SqlParameter("@ParentID", SqlDbType.Int);
            paras[3] = new SqlParameter("@Remark", SqlDbType.VarChar, 500);
            paras[4] = new SqlParameter("@ID", SqlDbType.Int);

            paras[0].Value = dept.CName;
            paras[1].Value = dept.EName;
            if (dept.ParentID == 0)
                paras[2].Value = DBNull.Value;
            else
                paras[2].Value = dept.ParentID;

            paras[3].Value = dept.Remark;
            paras[4].Value = dept.ID;
            using (SqlConnection conn = new SqlConnection(SqlHelper.baseConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        public Department GetDepartment(int id)
        {
            throw new NotImplementedException();
        }

        public IList<Department> GetDepartments()
        {
            IList<Department> list = new List<Department>();

            string sql = "select  [ID] ,[CName] ,isnull([EName],''),isnull([ParentID],0),isnull([Remark],'')  FROM [dbo].[Department]";

            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.baseConnectionString, CommandType.Text, sql, null))
            {
                while (sdr.Read())
                {
                    Department dept = new Department();
                    dept.ID = sdr.GetInt32(0);
                    dept.CName = sdr.GetString(1);
                    dept.EName = sdr.GetString(2);
                    dept.ParentID = sdr.GetInt32(3);
                    dept.Remark = sdr.GetString(4);
                    list.Add(dept);
                }
            }
            return list;
        }

        public IList<Department> GetDepartments(int pageIndex, int pageSize, out int total)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        ///  获取当前部门的第一级子部门
        /// </summary>
        /// <param name="deptment"></param>
        /// <returns></returns>
        public IList<Department> GetChildrens(Department deptment)
        {
            IList<Department> list = new List<Department>();

            string sql = "select  [ID] ,[CName] ,isnull([EName],''),isnull([ParentID],0),isnull([Remark],'')  FROM [dbo].[Department] where [ParentID]=@ParentID";

            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@ParentID", SqlDbType.Int);
            
            paras[0].Value = deptment.ID;

            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.baseConnectionString, CommandType.Text, sql, paras))
            {
                while (sdr.Read())
                {
                    Department dept = new Department();
                    dept.ID = sdr.GetInt32(0);
                    dept.CName = sdr.GetString(1);
                    dept.EName = sdr.GetString(2);
                    dept.ParentID = sdr.GetInt32(3);
                    dept.Remark = sdr.GetString(4);
                    list.Add(dept);
                }
            }
            return list;

        }


        public IList<Department> GetDepartmentsByLevel(int level)
        {
            IList<Department> list = new List<Department>();

            string sql = "select  [ID] ,[CName] ,isnull([EName],''),isnull([ParentID],0),isnull([Remark],'')  FROM [dbo].[Department]";

            if (level <= 0)
                sql += " where [ParentID] is null or  [ParentID]=0";
            SqlParameter[] paras = null;// new SqlParameter[1];
            //paras[0] = new SqlParameter("@ParentID", SqlDbType.Int);

            //paras[0].Value = deptment.ID;

            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.baseConnectionString, CommandType.Text, sql, paras))
            {
                while (sdr.Read())
                {
                    Department dept = new Department();
                    dept.ID = sdr.GetInt32(0);
                    dept.CName = sdr.GetString(1);
                    dept.EName = sdr.GetString(2);
                    dept.ParentID = sdr.GetInt32(3);
                    dept.Remark = sdr.GetString(4);
                    list.Add(dept);
                }
            }
            return list;
        }
    }
}
